/*cd "E:\My Drive\Wage_Rigidity\surveys"
global tables "./tables"
global log "./log"

set scheme plotplain
graph set window fontface "Times New Roman"

capture log close
log using "$log/2_table_surveys", replace*/

****************
*Import data
****************
import excel "$data/relevant_useful.xlsx", sheet("Sheet1") firstrow case(lower) clear
keep documentid useful-bonus comment
tempfile temp1 
save `temp1'

import excel "$data/other_useful.xlsx", sheet("Sheet1") firstrow case(lower) clear
keep documentid useful-bonus comment
tempfile temp2
save `temp2'

import excel "$data/attachments_useful.xlsx", sheet("Sheet1") firstrow case(lower) clear
keep documentid useful-bonus comment
tempfile temp3
save `temp3'

****************
*Append data
****************
 clear
 append using `temp1'
 append using `temp2'
 append using `temp3'

****************
*Summarize data
****************
*Share who say they reversed their actions
	preserve 
		keep if useful==1 // useful means they actually talk about what they did, and not just a complaint - can include non-emloyers due to letters from workers
		drop if retroactive==0
		keep if employer==1 // employer can include not useful if they talk about the costs of the policy, but not what they did

		foreach var of varlist bunch reclass cuthours raise_prices hire layoff retract_bunch retract_reclass delayed {
			replace `var'=0 if `var'==.
			replace `var'=1 if `var'!=0 // Some firms say the share of workers they reclassed vs. bunched, but for most observations, I just have a dummy
		}

		gen bunch2 = bunch==1 & retract_bunch==0 & delayed!=1
		sum bunch2
		local bunch = round(100*`r(mean)')

		gen reclass2 = reclass==1 & retract_reclass==0 & delayed!=1
		sum reclass2
		local reclass = round(100*`r(mean)')

		gen cuthours2=cuthours==1 & retract_reclass!=0 & delayed!=1
		sum cuthours2
		local hours = round(100*`r(mean)')

		sum delayed
		local delayed = round(100*`r(mean)')

		sum retract_bunch if  delayed!=1
		local retract1 = round(100*`r(mean)')

		sum retract_reclass if  delayed!=1
		local retract2 = round(100*`r(mean)')

		count
		local n=`r(N)'

	capture erase "$tables/surveys.tex"
	file open fh3 using "$tables/surveys.tex", write replace

	file write fh3 ///
		"\begin{tabular}{@{\hskip\tabcolsep\extracolsep\fill} l cc cc cc cc cc cc cc}" _n ///
        "\toprule" _n ///
        "&& (1) && (2) && (3) && (4) && (5) && (6) && (7) \\" _n ///
        "\midrule" _n ///
		"Already implemented changes&& 50\% && 49\% && 37\% && 31\% &&  &&  &&  \\" _n ///
		"\hspace{0.2cm} Pay raises&&  && 55\% && 28\% &&  &&  &&  &&  `bunch'\%\\" _n ///
		"\hspace{0.2cm} Reclassifications&&  && 98\% && 48\% &&  &&  &&  &&  `reclass'\%\\" _n ///
		"[1em] &&  &&  &&  &&  &&  &&  &&  \\" _n ///
		"Go forward as planned&&  &&  &&  && 25\% && 65\% && 28\% &&  \\" _n ///
		"[1em] &&  &&  &&  &&  &&  &&  &&  \\" _n ///
		"Implement some changes&&  &&  &&  && 21\% &&  && 32\% &&  \\" _n ///
		"\hspace{0.2cm} Go ahead with pay raises&&  &&  &&  &&  &&  && 70\% &&  \\" _n ///
		"\hspace{0.2cm} Go ahead with reclassifications&&  &&  &&  &&  &&  && 41\% &&   \\" _n ///
		"\hspace{0.2cm} Go ahead with changing hours&&  &&  &&  &&  &&  && 15\% && \\" _n ///
		"[1em] &&  &&  &&  &&  &&  &&  &&  \\" _n ///
		"Wait until final resolution of legal case&& 39\% &&  && 46\% && 24\% && 35\% && 32\% && `delayed'\% \\" _n ///
		"[1em] &&  &&  &&  &&  &&  &&  &&  \\" _n ///
		"No plans&& 11\% &&  &&  &&  &&  &&  &&  \\" _n ///
		"[1em] &&  &&  &&  &&  &&  &&  &&  \\" _n ///
		"Reversing some changes&&  &&  &&  &&  &&  && 8\% &&  \\" _n ///
		"\hspace{0.2cm} Reverse some or all pay raises &&  &&  &&  &&  &&  && 23\% && `retract1'\% \\" _n ///
		"\hspace{0.2cm} Reverse some or all reclassifications &&  &&  &&  &&  &&  && 95\% && `retract2'\%  \\" _n ///
		"\midrule&&  &&  &&  &&  &&  &&  &&  \\" _n ///
		"Date&& Post Injunction  && Post Injunction  && Post Injunction  && Nov 25-29, 2016 && Nov 25-29, 2016 && Dec 2016 && Sept 2017 \\" _n ///
		"Sample&& \multicolumn{1}{c}{\multirow{2}{*}{\begin{tabular}[c]{@{}c@{}}Multiple \\ industries\end{tabular}}} && Independent Schools && Franchises && Retail && \multicolumn{1}{c}{\multirow{2}{*}{\begin{tabular}[c]{@{}c@{}}Retail, conditional on \\ planned to raise salaries\end{tabular}}} && \multicolumn{1}{c}{\multirow{2}{*}{\begin{tabular}[c]{@{}c@{}}Colleges and \\ Universities\end{tabular}}} && Open Forum \\ \\" _n ///
		"Survey&& Littler Mendelson && NAIS and NBOA && IFA && Korn Ferry && Korn Ferry && CUPA HR && DOL RFI \\" _n ///
		"N&& $\sim$900 && 369 && $\sim$300 && 68 && 68 && 495 && `n' \\" _n ///
		"\bottomrule" _n ///
		"\end{tabular}" _n
		
	file close fh3 
	macro drop fh3 

	restore

*Share who claim flexibility as a cost
	preserve 
	keep if employer==1 
	*replace flexible_hours=1 if productivity==1 | seasonal==1 | fluctuations==1 | morale==1 | monitoring==1 

	foreach var of varlist cannotafford flexible_hours productivity seasonal fluctuations monitoring morale {
			replace `var'=0 if `var'==.
			replace `var'=1 if `var'!=0 // Some firms say the share of workers they reclassed vs. bunched, but for most observations, I just have a dummy
		}

	sum cannotafford
	local costs = round(100*`r(mean)')
	local n=`r(N)'

	sum flexible_hours
	local flex= round(100*`r(mean)')

	sum productivity if flexible_hours==1
	local flex2= round(100*`r(mean)')

	sum seasonal if flexible_hours==1
	local flex3= round(100*`r(mean)')

	sum fluctuations if flexible_hours==1
	local flex4= round(100*`r(mean)')

	sum monitoring if flexible_hours==1
	local flex5= round(100*`r(mean)')

	sum morale if flexible_hours==1
	local flex6= round(100*`r(mean)')

	capture erase "$tables/costs.tex"
	file open fh3 using "$tables/costs.tex", write replace

	file write fh3 ///
		"\begin{tabular}{@{\hskip\tabcolsep\extracolsep\fill} l cc cc cc }" _n ///
        "\toprule" _n ///
		"&& (1) && (2) && (3)  \\" _n ///
		"\midrule &&  &&  &&  \\" _n ///
		"Overtime Costs	&& 73\% && 75\% \\" _n ///
		"Training Costs	&& 17\% && 17\% \\" _n ///
		"Timekeeping && 53\% && 63\% \\" _n ///
		"Managerial Cost of supervising non-exempt && 38\% && 38\% \\" _n ///
		"Modifying policies	&& 53\% && 49\% \\" _n ///
		"Change benefits && 19\% && 26\% \\" _n ///
		"Travel time && 24\%	 \\" _n ///
		"Budget	&& && && `costs'\% \\" _n ///
		"Flexibility && && && `flex'\% \\ " _n ///
		"\hspace{0.2cm} Productivity && && && `flex2'\% \\ " _n ///
		"\hspace{0.2cm} Seasonal && && && `flex3'\% \\ " _n ///
		"\hspace{0.2cm} Unexpected Events && && && `flex4'\% \\ " _n ///
		"\hspace{0.2cm} Monitoring && && && `flex5'\% \\ " _n ///
		"\hspace{0.2cm} Morale && && && `flex6'\% \\ " _n ///
		"\midrule		 " _n ///
		"Date && Post Injunction	&& Post Injunction && Sept 2017 \\" _n ///
		"Sample	&& All	&& Franchises && Anyone \\" _n ///
		"Survey	&& Littler Mendelson && IFA && RFI \\" _n ///
		"N	&& ~900 && ~300 && `n' \\" _n ///
		"\bottomrule" _n ///
		"\end{tabular}" _n
	file close fh3 
	macro drop fh3 
	restore 

*Does comment mention fairness, 
	keep if employer==1 | employer==0
	gen fair=strpos(comment, "fair")>0 & strpos(comment, "Fair Labor Standard")>0
	gen morale2=strpos(comment, "morale")>0
	gen promise=strpos(comment, " promise")>0 // space to avoid words like compromise
	gen reputation=strpos(comment, "reputation")>0
	sum fair morale2 promise reputation if employer==1 & comment!="See Attached"
	sum fair morale2 promise reputation if employer==0 & comment!="See Attached" // "Promise" is all about Trump or Obama's campiaign promise to workers
	count if comment=="See Attached"

	bro if promise==1 & employer==0
	gen reverse=strpos(comment, "reverse")>0 | strpos(comment, "revert")>0
	bro if reverse==1 & employer==1

*All the firms that bunch workers
	bro if bunch==1 & employer==1 & useful==1 
	bro if bunch!=0 & bunch!=1 & bunch!=. & employer==1 & useful==1 
	count if bunch==1 & employer==1 & useful==1 & retract_bunch!=1 & retroactive!=0
